﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Text;
using ReflectionStudio.Core.Events;
namespace WPFExcelReport
{

    public class YARNDNData : BaseFunction
    {
        
        public YARNDNData()
            : base()
        {
           
        }
        public YARNDNData(Dictionary<string, object> DicGlobalPara)
            : base(DicGlobalPara)
        {
        }
        public DataSet GetRawData(DataRow dwABPOS)
        {
            DataSet set4Return = new DataSet();
            DataSet setLocal = new DataSet();
            DataSet setResult = new DataSet();
            DataSet setTemp = new DataSet();
            DataTable tableLocal = null;
            string FKRS, FLFD,FPOS;
            FKRS = dwABPOS["FKRS"].ToString().Trim();
            FLFD = dwABPOS["FLFD"].ToString().Trim();
            FPOS = dwABPOS["FPOS"].ToString().Trim();
            try
            {
                OracleCommand or1 = OracleDB.CreateCommand();
                #region

                /*or1.CommandText = "select ISSUEDAT,SPACE,SPINNINGLOT,DYELOT,ARTICLE,COLOR,COLORDES, "
                    + " WORDERNO,SORDERNO,SUPPLIER,SUM(WEI) AS WEI,FACTORY,REQARTICLE,QTY "
                    + " from (SELECT distinct A.GABBUCHDAT AS ISSUEDAT,I.GABLO||'-'||I.GABLB||'-'||I.GABLP as SPACE, "
                    + " A.GABPARTIE  AS BATCHNO,D.GPSPPART  AS SPINNINGLOT,D.GPFBPART AS DYELOT , "
                    + " SUBSTR(A.GABART,1,5)||'#'||TRIM(J.GSGARNBEZ) AS ARTICLE, "
                    + " TRIM(A.GABFARBE) AS COLOR,trim(K.GSCBEZ) AS COLORDES, "
                    + " B.ABPAUFKRS||'-'||TO_NUMBER(B.ABPAUFLFD)||'-'||TO_NUMBER(B.ABPAUFPOS) AS WORDERNO, "
                    + " NVL(DECODE(B.ABPAUFART,'GP','-','VW','-', "
                    + " F.ABAAUFKRS||'-'||to_number(F.ABAAUFLFD)||'-'||to_number(F.ABAAUFPOS)),'-') AS SORDERNO, "
                    + " TRIM(E.LSNAME1) AS SUPPLIER ,A.GABKG*0.001 AS WEI,trim(G.LSNAME1) AS FACTORY, "
                    + " TRIM(B.ABPART)||'-'||TRIM(B.ABPVAR)||'-'||B.ABPFARBE AS REQARTICLE,'' AS QTY "
                    + " FROM GABEW A,ABPOS B,(SELECT DISTINCT ABFMANDANT,ABFAUFKRS,ABFAUFLFD,ABFAUFPOS,ABFLIEFNR "
                    + " FROM ABFOLGE WHERE TRIM(ABFLIEFNR) IS NOT NULL) C,GPDAT D, "
                    + " LIEFDAT E,(SELECT A.ABAMANDANT,A.ABAAUFKRS,A.ABAAUFLFD,A.ABAAUFPOS, "
                    + " A.ABAVKAUFKRS,A.ABAVKAUFLFD,A.ABAVKAUFPOS FROM ABAUFM A  "
                    + " WHERE A.ABAAUFKRS LIKE 'S%')  F,GABEW I,GSDAT J,GSCOLOR K,LIEFDAT G  "
                    + " WHERE A.GABMANDANT = D.GPMANDANT  AND A.GABPARTIE = D.GPPARTIE "
                    + "  AND A.GABPARTIEKZ = D.GPPARTIEKZ  AND A.GABMANDANT = B.ABPMANDANT"
                    + "  AND A.GABAUFKRS = B.ABPAUFKRS  AND A.GABAUFLFD = B.ABPAUFLFD "
                    + "  AND A.GABAUFPOS = B.ABPAUFPOS  AND A.GABMANDANT = C.ABFMANDANT "
                    + "  AND A.GABAUFKRS = C.ABFAUFKRS  AND A.GABAUFLFD = C.ABFAUFLFD "
                    + "  AND A.GABAUFPOS = C.ABFAUFPOS  AND C.ABFMANDANT = E.LSMANDANT "
                    + "  AND C.ABFLIEFNR = E.LSLIEFNR  AND A.GABMANDANT = F.ABAMANDANT(+) "
                    + "  AND A.GABAUFKRS = F.ABAVKAUFKRS(+)  AND A.GABAUFLFD = F.ABAVKAUFLFD(+) "
                    + "  AND A.GABAUFPOS = F.ABAVKAUFPOS(+) "
                    + "  AND A.GABMANDANT = I.GABMANDANT AND A.GABAUFKRS = I.GABAUFKRS "
                    + "  AND A.GABAUFLFD = I.GABAUFLFD AND A.GABAUFPOS = I.GABAUFPOS "
                    + "  AND A.GABPARTIE = I.GABPARTIE AND I.GABBS = '61' "
                    + "  AND A.GABMANDANT = J.GSMANDANT AND A.GABART = J.GSART "
                    + "  AND A.GABVAR = J.GSVAR AND A.GABMANDANT = K.GSCMANDANT(+) "
                    + "  AND A.GABART = K.GSCART(+) AND A.GABVAR = K.GSCVAR(+) "
                    + "  AND D.GPMANDANT = G.LSMANDANT(+) AND D.GPLIEFNR = G.LSLIEFNR(+) "
                    + "  AND A.GABFARBE = K.GSCFARBE(+)  AND A.GABBS = '22'  AND A.GABMANDANT = '00' "
                    + "  AND I.GABLB LIKE '1%' AND A.GABBUCHDAT = TO_CHAR(SYSDATE,'YYYYMMDD') "
                    + "  AND A.GABAUFKRS = '" + FKRS + "' AND A.GABAUFLFD = '" + FLFD + "' AND A.GABAUFPOS ='" + FPOS + "'"
                    + "  ) GROUP BY ISSUEDAT,SPACE,SPINNINGLOT,DYELOT,ARTICLE,COLOR,COLORDES, "
                    + " WORDERNO,SORDERNO,SUPPLIER,FACTORY,REQARTICLE,QTY  "
                    + " order by article,spinninglot " ; */
                   
                #endregion
                #region
                or1.CommandText = " select ISSUEDAT,SPACE,SPINNINGLOT,DYELOT,ARTICLE,COLOR,COLORDES,WORDERNO,SORDERNO, "
                    + " SUPPLIER,SUM(WEI) AS WEI,FACTORY,REQARTICLE,QTY  from ( "
                    + " SELECT distinct A.GABBUCHDAT AS ISSUEDAT,I.GABLO || '-' || I.GABLB || '-' || I.GABLP as SPACE, "
                    + " A.GABPARTIE AS BATCHNO,D.GPSPPART AS SPINNINGLOT,D.GPFBPART AS DYELOT, "
                    + " SUBSTR(A.GABART, 1, 5) || '#' || TRIM(GSGARNBEZ)||TRIM(GSHANDLNR) AS ARTICLE,TRIM(A.GABFARBE) AS COLOR, "
                    + " trim(K.GSCBEZ) AS COLORDES, "
                    + " B.ABPAUFKRS || '-' || TO_NUMBER(B.ABPAUFLFD) || '-' ||TO_NUMBER(B.ABPAUFPOS) AS WORDERNO, "
                    + " NVL(DECODE(B.ABPAUFART,'GP','-','VW','-',F.ABAAUFKRS || '-' || "
                    + " to_number(F.ABAAUFLFD) || '-' ||to_number(F.ABAAUFPOS)),'-') AS SORDERNO, "
                    + " TRIM(E.LSNAME1) AS SUPPLIER,A.GABKG * 0.001 AS WEI,DECODE(B.ABPAUFART,'VW',trim(G.LSNAME1),'') AS FACTORY, "
                    + " TRIM(B.ABPART) || '-' || TRIM(B.ABPFARBE) || '-' ||B.ABPVAR AS REQARTICLE, "
                    + " '' AS QTY "
                    + " FROM GABEW A,ABPOS B,(SELECT DISTINCT ABFMANDANT,ABFAUFKRS, "
                    + " ABFAUFLFD,ABFAUFPOS,ABFLIEFNR FROM ABFOLGE  WHERE TRIM(ABFLIEFNR) IS NOT NULL) C, "
                    + "  GPDAT D,LIEFDAT E,(SELECT A.ABAMANDANT,A.ABAAUFKRS,A.ABAAUFLFD,A.ABAAUFPOS, "
                    + "  A.ABAVKAUFKRS,A.ABAVKAUFLFD,A.ABAVKAUFPOS FROM ABAUFM A "
                    + " WHERE A.ABAAUFKRS LIKE 'S%') F,GABEW I,GSDAT J,GSCOLOR K,LIEFDAT G "
                    + " WHERE A.GABMANDANT = D.GPMANDANT AND A.GABPARTIE = D.GPPARTIE "
                    + " AND A.GABPARTIEKZ = D.GPPARTIEKZ AND A.GABMANDANT = B.ABPMANDANT AND A.GABAUFKRS = B.ABPAUFKRS "
                    + " AND A.GABAUFLFD = B.ABPAUFLFD AND A.GABAUFPOS = B.ABPAUFPOS AND A.GABMANDANT = C.ABFMANDANT "
                    + " AND A.GABAUFKRS = C.ABFAUFKRS AND A.GABAUFLFD = C.ABFAUFLFD AND A.GABAUFPOS = C.ABFAUFPOS "
                    + " AND C.ABFMANDANT = E.LSMANDANT AND C.ABFLIEFNR = E.LSLIEFNR AND A.GABMANDANT = F.ABAMANDANT(+) "
                    + " AND A.GABAUFKRS = F.ABAVKAUFKRS(+) AND A.GABAUFLFD = F.ABAVKAUFLFD(+) AND A.GABAUFPOS = F.ABAVKAUFPOS(+) "
                    + " AND A.GABMANDANT = I.GABMANDANT  AND A.GABJJJJ = I.GABJJJJ  AND A.GABBELEG = I.GABBELEG "
                    + " AND A.GABPARTIE = I.GABPARTIE AND A.GABPARTIEKZ = I.GABPARTIEKZ AND I.GABBS = '20' "
                    + " AND A.GABMANDANT = J.GSMANDANT AND A.GABART = J.GSART AND A.GABVAR = J.GSVAR "
                    + " AND A.GABMANDANT = K.GSCMANDANT(+) AND A.GABART = K.GSCART(+) AND A.GABVAR = K.GSCVAR(+) "
                    + " AND D.GPMANDANT = G.LSMANDANT(+) AND D.GPLIEFNR = G.LSLIEFNR(+) AND A.GABFARBE = K.GSCFARBE(+) "
                    + " AND A.GABBS = '21' AND A.GABMANDANT = '00' AND I.GABLB LIKE '1%' "
                    + " AND A.GABBUCHDAT = TO_CHAR(SYSDATE, 'YYYYMMDD')  "
                    + " AND A.GABAUFKRS = '" + FKRS + "' AND A.GABAUFLFD = '" + FLFD + "' AND A.GABAUFPOS ='" + FPOS + "' "
                    + " UNION ALL "
                    + " SELECT A.GABBUCHDAT AS ISSUEDAT,(select MIN(T.GABLO) FROM GABEW T "
                    + " WHERE T.GABMANDANT = A.GABMANDANT AND T.GABAUFKRS = A.GABAUFKRS AND T.GABAUFLFD = A.GABAUFLFD AND T.GABAUFPOS = A.GABAUFPOS "
                    + " AND T.GABPARTIE = A.GABPARTIE AND T.GABBS = '61' AND T.GABLB LIKE '1%' )||'-'|| "
                    + " (select MIN(T.GABLB) FROM GABEW T WHERE T.GABMANDANT = A.GABMANDANT AND T.GABAUFKRS = A.GABAUFKRS "
                    + " AND T.GABAUFLFD = A.GABAUFLFD AND T.GABAUFPOS = A.GABAUFPOS "
                    + " AND T.GABPARTIE = A.GABPARTIE AND T.GABBS = '61' AND T.GABLB LIKE '1%' )||'-'|| "
                    + " (select MIN(T.GABLP) FROM GABEW T WHERE T.GABMANDANT = A.GABMANDANT AND T.GABAUFKRS = A.GABAUFKRS "
                    + " AND T.GABAUFLFD = A.GABAUFLFD AND T.GABAUFPOS = A.GABAUFPOS AND T.GABPARTIE = A.GABPARTIE AND T.GABBS = '61' "
                    + " AND T.GABLB LIKE '1%' ) as SPACE,A.GABPARTIE AS BATCHNO, D.GPSPPART AS SPINNINGLOT,D.GPFBPART AS DYELOT, "
                    + " SUBSTR(A.GABART, 1, 5) || '#' || TRIM(GSGARNBEZ)||TRIM(GSHANDLNR) AS ARTICLE,TRIM(A.GABFARBE) AS COLOR, "
                    + " trim(K.GSCBEZ) AS COLORDES,B.ABPAUFKRS || '-' || TO_NUMBER(B.ABPAUFLFD) || '-' ||TO_NUMBER(B.ABPAUFPOS) AS WORDERNO, "
                    + " NVL(DECODE(B.ABPAUFART,'GP','-','VW','-',F.ABAAUFKRS || '-' || "
                    + " to_number(F.ABAAUFLFD) || '-' ||to_number(F.ABAAUFPOS)),'-') AS SORDERNO, "
                    + " TRIM(E.LSNAME1) AS SUPPLIER,A.GABKG * 0.001 AS WEI,DECODE(B.ABPAUFART,'VW',trim(G.LSNAME1),'') AS FACTORY, "
                    + " TRIM(B.ABPART) || '-' || TRIM(B.ABPFARBE) || '-' ||B.ABPVAR AS REQARTICLE, "
                    + " '' AS QTY "
                    + " FROM GABEW A,ABPOS B,(SELECT DISTINCT ABFMANDANT,ABFAUFKRS, "
                    + " ABFAUFLFD,ABFAUFPOS,ABFLIEFNR FROM ABFOLGE  WHERE TRIM(ABFLIEFNR) IS NOT NULL) C, "
                    + "  GPDAT D,LIEFDAT E,(SELECT A.ABAMANDANT,A.ABAAUFKRS,A.ABAAUFLFD,A.ABAAUFPOS, "
                    + "  A.ABAVKAUFKRS,A.ABAVKAUFLFD,A.ABAVKAUFPOS FROM ABAUFM A "
                    + " WHERE A.ABAAUFKRS LIKE 'S%') F,GSDAT J,GSCOLOR K,LIEFDAT G "
                    + " WHERE A.GABMANDANT = D.GPMANDANT  AND A.GABPARTIE = D.GPPARTIE AND A.GABPARTIEKZ = D.GPPARTIEKZ "
                    + " AND A.GABMANDANT = B.ABPMANDANT AND A.GABAUFKRS = B.ABPAUFKRS AND A.GABAUFLFD = B.ABPAUFLFD "
                    + " AND A.GABAUFPOS = B.ABPAUFPOS AND A.GABMANDANT = C.ABFMANDANT AND A.GABAUFKRS = C.ABFAUFKRS "
                    + " AND A.GABAUFLFD = C.ABFAUFLFD AND A.GABAUFPOS = C.ABFAUFPOS AND C.ABFMANDANT = E.LSMANDANT "
                    + " AND C.ABFLIEFNR = E.LSLIEFNR AND A.GABMANDANT = F.ABAMANDANT(+) AND A.GABAUFKRS = F.ABAVKAUFKRS(+) "
                    + " AND A.GABAUFLFD = F.ABAVKAUFLFD(+) AND A.GABAUFPOS = F.ABAVKAUFPOS(+) AND A.GABMANDANT = J.GSMANDANT "
                    + " AND A.GABART = J.GSART AND A.GABVAR = J.GSVAR  AND A.GABMANDANT = K.GSCMANDANT(+) "
                    + " AND A.GABART = K.GSCART(+)  AND A.GABVAR = K.GSCVAR(+) AND D.GPMANDANT = G.LSMANDANT(+) "
                    + " AND D.GPLIEFNR = G.LSLIEFNR(+) AND A.GABFARBE = K.GSCFARBE(+) AND A.GABBS = '22' "
                    + " AND A.GABMANDANT = '00' AND A.GABBUCHDAT = TO_CHAR(SYSDATE, 'YYYYMMDD') "
                    + " AND A.GABAUFKRS = '" + FKRS + "' AND A.GABAUFLFD = '" + FLFD + "' AND A.GABAUFPOS ='" + FPOS + "' "
                    + " AND not exists ( SELECT '1' FROM GABEW A1,GPDAT D1,ABPOS B1,GABEW F1,LAGPL L1 "
                    + " WHERE A1.GABMANDANT = D1.GPMANDANT  AND A1.GABPARTIE = D1.GPPARTIE "
                    + " AND A1.GABPARTIEKZ = D1.GPPARTIEKZ AND A1.GABMANDANT = B1.ABPMANDANT "
                    + " AND A1.GABAUFKRS = B1.ABPAUFKRS  AND A1.GABAUFLFD = B1.ABPAUFLFD  AND A1.GABAUFPOS = B1.ABPAUFPOS "
                    + " AND A1.GABMANDANT = F1.GABMANDANT  AND A1.GABJJJJ = F1.GABJJJJ AND A1.GABBELEG = F1.GABBELEG "
                    + " AND A1.GABPARTIE = F1.GABPARTIE AND A1.GABPARTIEKZ = F1.GABPARTIEKZ  AND F1.GABMANDANT = A.GABMANDANT "
                    + " AND F1.GABPARTIE = A.GABPARTIE AND F1.GABPARTIEKZ = A.GABPARTIEKZ AND F1.GABLBEHI = A.GABLBEHI "
                    + " AND F1.GABAUFKRS = A.GABAUFKRS  AND F1.GABAUFLFD = A.GABAUFLFD AND F1.GABAUFPOS = A.GABAUFPOS "
                    + " AND F1.GABBUCHDAT = A.GABBUCHDAT AND A1.GABLO = L1.LPLO AND A1.GABLB = L1.LPLB "
                    + " AND A1.GABLP = L1.LPPLATZ AND L1.LPLBKZ = 'L' AND A1.GABBS = '20'  AND F1.GABBS = '21') "
                    + " ) GROUP BY ISSUEDAT, SPACE, SPINNINGLOT, DYELOT, ARTICLE, COLOR, "
                    + " COLORDES,WORDERNO,SORDERNO,SUPPLIER, FACTORY,REQARTICLE,QTY order by article, spinninglot "
                    ;
                #endregion
                setLocal = Connect2Proxy(or1);
                if (setLocal.Tables.Count > 0)
                {
                    tableLocal = setLocal.Tables[0];
                    tableLocal.TableName = "YARNDN";
                    
                    byte[] b;
                    string value;
                    string ColumnName;
                    foreach (DataRow dwTemp in setLocal.Tables["YARNDN"].Rows)
                    {
                        ColumnName = "ARTICLE";
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[ColumnName].ToString());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dwTemp[ColumnName] = value;

                        ColumnName = "COLORDES";
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[ColumnName].ToString());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dwTemp[ColumnName] = value;

                        ColumnName = "SUPPLIER";
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[ColumnName].ToString());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dwTemp[ColumnName] = value; 

                        ColumnName = "FACTORY";
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[ColumnName].ToString());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dwTemp[ColumnName] = value;
                       
                       
                    }
                    
                    
                    set4Return.Merge(setLocal);
                }
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return set4Return;
        }        
        public DataSet BuildGBBEW(DataRow dwUI)
        {
            string progress = "查询完成";
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            try
            {
                EventDispatcher.Instance.RaiseStatus("查询开始", StatusEventType.StartProgress);
                setResult = GetRawData(dwUI);
                setReturn.Merge(setResult);
                EventDispatcher.Instance.RaiseStatus("查询完成", StatusEventType.StopProgress);
            }
            catch (Exception ex)
            {
                Tracer.Error(ABPOS + ex.StackTrace, ex);
            }
            return setReturn;
        }
    }
}